import openpyxl


class OperateExcel:

    def __init__(self, file):
        self.file = file

    def open_excel(self):
        """打开excel"""
        self.wb = openpyxl.load_workbook(self.file)
        # self.sh = self.wb[self.sheet]
        return self.wb

    def get_sheet(self, sheet):
        self.open_excel()
        self.sh = self.wb.get_sheet_by_name(sheet)
        return self.sh

    def get_sheet_all_data(self,sheet):
        """
        获取sheet中所有数据
        :param sheet:
        :return:
        """
        self.get_sheet(sheet)
        self.datas = list(self.sh.rows)  # excel中全部的数据
        return self.datas

    def get_sheet_title(self,sheet):
        """
        获取sheet的首行
        :param sheet:
        :return:[title1,title2]
        """
        datas = self.get_sheet_all_data(sheet)
        self.title = [i.value for i in datas[0]]
        return self.title

    def read_excel(self, sheet):
        """
        读excel的数据
        :param sheet:
        :return:
        """
        datas = self.get_sheet_all_data(sheet)  # excel中全部的数据
        title = self.get_sheet_title(sheet)  # excel中的首行数据
        cases = []
        for i in datas[1:]:
            data = [k.value for k in i]
            case = dict(zip(title, data))
            cases.append(case)
        return cases

    def write_excel(self, row, column, value, sheet):
        """往excel中写入数据"""
        self.get_sheet(sheet)
        self.sh.cell(row=row, column=column, value=value)
        self.wb.save(self.file)
